ALTER PROC [dbo].[GET_DATA_REPORT]
@USR_ID INT, 
@FROM_DATE DATE, 
@TO_DATE DATE
AS
BEGIN
 SELECT 
	 V_MONEY_REPORT.NGUOI_THU,
	 USR.FULL_NAME, 
	 V_MONEY_REPORT.NGAY_THU,
	 SUM(V_MONEY_REPORT.TIEN_PHONG) TIEN_PHONG, 
	 SUM(V_MONEY_REPORT.TIEN_DIEN) TIEN_DIEN, 
	 SUM(V_MONEY_REPORT.TIEN_NUOC) TIEN_NUOC, 
	 SUM(V_MONEY_REPORT.PHU_THU) PHU_THU, 
	 SUM(V_MONEY_REPORT.CHI_TIEU) CHI_TIEU, 
	 SUM(V_MONEY_REPORT.THU_TIEN_COC) THU_TIEN_COC, 
	 SUM(V_MONEY_REPORT.TRA_TIEN_COC) TRA_TIEN_COC, 
	 SUM(V_MONEY_REPORT.TIEN_PHONG) 
	 + SUM(V_MONEY_REPORT.TIEN_DIEN) 
	 + SUM(V_MONEY_REPORT.TIEN_NUOC) 
	 + SUM(V_MONEY_REPORT.PHU_THU) 
	 - SUM(V_MONEY_REPORT.CHI_TIEU)
	 + SUM(V_MONEY_REPORT.THU_TIEN_COC)
	 - SUM(V_MONEY_REPORT.TRA_TIEN_COC) TIEN_MAT
 FROM 
	V_MONEY_REPORT,
	USR
 WHERE 
	(@USR_ID = 0 OR V_MONEY_REPORT.NGUOI_THU = @USR_ID)
	AND (@FROM_DATE IS NULL OR CAST(V_MONEY_REPORT.NGAY_THU AS DATE) >= CAST(@FROM_DATE AS DATE))	
	AND (@TO_DATE IS NULL OR CAST(V_MONEY_REPORT.NGAY_THU AS DATE) <= CAST(@TO_DATE AS DATE))
	AND V_MONEY_REPORT.NGUOI_THU = USR.ID 
 GROUP BY 	
 V_MONEY_REPORT.NGUOI_THU,
 USR.FULL_NAME, 
 V_MONEY_REPORT.NGAY_THU
 ORDER BY V_MONEY_REPORT.NGUOI_THU
END


ALTER PROC [dbo].[SP_GET_PHONG]
@SO_PHONG VARCHAR(20),
@TINH_TRANG INT
AS
BEGIN
	SELECT 
		ID,
		SO_PHONG,
		ISNULL(DON_GIA,0) DON_GIA, 
		ISNULL(DIEN_TICH,0) DIEN_TICH, 
		GHI_CHU, 
		DBO.F_GET_ROOM_STATUS(ID) TINH_TRANG
	FROM 
	PHONG
	WHERE
	(@SO_PHONG IS NULL OR @SO_PHONG = '' OR UPPER(PHONG.SO_PHONG) LIKE '%' + UPPER(@SO_PHONG) + '%')
	AND (@TINH_TRANG = 0 OR 
		(@TINH_TRANG = 1 AND PHONG.ID NOT IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)) OR
		(@TINH_TRANG = 2 AND PHONG.ID IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)))
	AND PHONG.ACTIVE = 'A'
	ORDER BY DBO.F_GET_ROOM_STATUS(ID)
END


ALTER PROC [dbo].[GET_DATA_REPORT]
@USR_ID INT, 
@FROM_DATE DATE, 
@TO_DATE DATE
AS
BEGIN
 SELECT 
	 V_MONEY_REPORT.NGUOI_THU,
	 USR.FULL_NAME, 
	 V_MONEY_REPORT.NGAY_THU,
	 SUM(V_MONEY_REPORT.TIEN_PHONG) TIEN_PHONG, 
	 SUM(V_MONEY_REPORT.TIEN_DIEN) TIEN_DIEN, 
	 SUM(V_MONEY_REPORT.TIEN_NUOC) TIEN_NUOC, 
	 SUM(V_MONEY_REPORT.PHU_THU) PHU_THU, 
	 SUM(V_MONEY_REPORT.CHI_TIEU) CHI_TIEU, 
	 SUM(V_MONEY_REPORT.THU_TIEN_COC) THU_TIEN_COC, 
	 SUM(V_MONEY_REPORT.TRA_TIEN_COC) TRA_TIEN_COC, 
	 SUM(V_MONEY_REPORT.TIEN_PHONG) 
	 + SUM(V_MONEY_REPORT.TIEN_DIEN) 
	 + SUM(V_MONEY_REPORT.TIEN_NUOC) 
	 + SUM(V_MONEY_REPORT.PHU_THU) 
	 - SUM(V_MONEY_REPORT.CHI_TIEU)
	 + SUM(V_MONEY_REPORT.THU_TIEN_COC)
	 - SUM(V_MONEY_REPORT.TRA_TIEN_COC) TIEN_MAT
 FROM 
	V_MONEY_REPORT,
	USR
 WHERE 
	(@USR_ID = 0 OR V_MONEY_REPORT.NGUOI_THU = @USR_ID)
	AND (@FROM_DATE IS NULL OR CAST(V_MONEY_REPORT.NGAY_THU AS DATE) >= CAST(@FROM_DATE AS DATE))	
	AND (@TO_DATE IS NULL OR CAST(V_MONEY_REPORT.NGAY_THU AS DATE) <= CAST(@TO_DATE AS DATE))
	AND V_MONEY_REPORT.NGUOI_THU = USR.ID 
 GROUP BY 	
 V_MONEY_REPORT.NGUOI_THU,
 USR.FULL_NAME, 
 V_MONEY_REPORT.NGAY_THU
 ORDER BY V_MONEY_REPORT.NGUOI_THU
END
